﻿/****************************** Module Header ******************************\
 * Module Name:  DatabaseHelper.cs
 * Module Version:  1.0.0 (2011-11-1)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to operate database. It supplies public method to execute 
 * a sql command or get a common connection or get the database structure infomation.
 * 
 * log:2012-8-14 by sutroon
 * 1.string[] ConnectionInfo 替换成 ConnectionStringSettings ConnectionSetting
 * 2.删除ProviderName和ConnectionInfo等属性
 * log:2012-11-27 by sutroon
 * 重构,由DBHelper改为DatabaseHelper
 * log:2013-2-18 by sutroon
 * 增加错误消息收集器
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace UCL.Data
{
    /// <summary>
    /// SQLServerHelper数据库操纵类
    /// <para>提供获取连接对象、执行命令、获取数据库架构信息等方法</para>
    /// <para>警告：坚决拒绝使用返回DataReader对象！</para>
    /// </summary>
    [ChangeLog(Author = "sutroon", Date = "2013-6-25", Ver = "2.2.0", Log = "新增DatabaseName、HostName等属性")]
    [ChangeLog(Author = "sutroon", Date = "2013-2-18", Ver = "2.1.0", Log = "增加错误消息收集器")]
    [ChangeLog(Author = "sutroon", Date = "2012-11-27", Ver = "2.0.0", Log = "重构,由DBHelper改为DatabaseHelper")]
    [ChangeLog(Author = "sutroon", Date = "2012-8-14", Ver = "1.2.0", Log = "1.string[] ConnectionInfo 替换成 ConnectionStringSettings ConnectionSetting;2.删除ProviderName和ConnectionInfo等属性")]
    [ChangeLog(Author = "sutroon", Date = "2011-11-1", Ver = "1.0.0", Log = "创建类")]
    public class SQLServerHelper : IDatabaseHelper
    {
        /// <summary>
        /// 获取或设置当前连接的数据库名称
        /// </summary>
        public string DatabaseName { get; set; }

        /// <summary>
        /// 获取或设置当前连接的数据库服务器名称
        /// </summary>
        public string ServerName { get; set; }

        /// <summary>
        /// 获取或设置当前处理的数据表名称
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 获取或设置数据库连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 获取数据源提供程序名称
        /// </summary>
        public string ProviderName { get { return "System.Data.SqlClient"; } }

        /// <summary>
        /// 获取或设置消息收集器
        /// </summary>
        public IList<string> MessageSet { get; set; }

        /// <summary>
        /// 获取或设置是否启用调试模式,如果启用，则错误消息将包含敏感信息
        /// </summary>
        public bool IsDebug { get; set; }

        /// <summary>
        /// 构造方法
        /// </summary>
        public SQLServerHelper(string connectionString)
        {
            this.ConnectionString = connectionString;
            this.MessageSet = new List<string>();
        }

        #region 数据库普通操纵方法

        /// <summary>
        /// 执行一条命令并返回受影响的行数
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, DbParameter[] paras = null)
        {
            if (string.IsNullOrEmpty(commandText)) return 0;
            int intOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    conn.Open();
                    try
                    {
                        intOut = cmd.ExecuteNonQuery();
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return intOut;
        }

        /// <summary>
        /// 执行命令并返回一个数据表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, DbParameter[] paras = null)
        {
            DataTable dt = new DataTable();
            if (string.IsNullOrEmpty(commandText)) return dt;
            using (DbCommand cmd = GetDbCommand())
            {
                cmd.CommandText = commandText;
                if (paras != null) cmd.Parameters.AddRange(paras);
                DbDataAdapter dbda = new SqlDataAdapter();
                dbda.SelectCommand = cmd;
                try
                {
                    cmd.Connection.Open();
                    dbda.Fill(dt);
                }
                catch (Exception ex)
                {
                    if (IsDebug) MessageSet.Add(commandText);
                    MessageSet.Add(ex.Message);
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取分页数据表 2013-6-10 by sutroon
        /// </summary>
        /// <param name="fromsql">如：from tmytest where State=1</param>
        /// <param name="orderby">如：CreatedTime desc</param>
        /// <param name="fields">如：*</param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable ExecutePagingDataTable(string fromsql, string orderby, string fields, out int recordcount, out int pagecount, int pageid = 1, int pagesize = 20)
        {
            recordcount = Convert.ToInt32(this.GetValue(string.Format("select count(0) {0};", fromsql)));
            if (recordcount == 0)
            {
                pagecount = 0;
                return new DataTable();
            }
            pagecount = recordcount / pagesize;
            if (recordcount % pagesize > 0) pagecount += 1;
            return this.ExecuteDataTable(string.Format("select {0} {1} {2} limit {3},{4};", fields, fromsql, orderby, ((pageid - 1) * pagesize), pagesize));
        }

        /// <summary>
        /// 执行命令并返回一个数据集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string commandText, DbParameter[] paras = null)
        {
            DataSet ds = new DataSet();
            if (string.IsNullOrEmpty(commandText)) return ds;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    DbDataAdapter dbda = new SqlDataAdapter();
                    dbda.SelectCommand = cmd;
                    conn.Open(); try
                    {
                        dbda.Fill(ds);
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 递归获取分类数据表,数据主要用于DropDownList控件
        /// </summary>
        /// <param name="sqlQuery">查询语句，如<para>select id,name from catalog where parentid={0} and innercat='album' order by Ordinal</para></param>
        /// <param name="parentID">父编号</param>
        /// <param name="dataTable">用户填充数据的空数据表</param>
        /// <param name="paddingChar">名称前面分级填充的字符，如 '-'</param>
        /// <param name="loopCount">递归次数,默认值是0</param>
        public DataTable ExecuteTreeTable(string sqlQuery, string parentID, char paddingChar, int loopCount, string topID = "0", string topName = "请选择")
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            dt.Columns.Add("Level");
            if (!string.IsNullOrEmpty(topName))
            {
                dt.Rows.Add(topID, topName, 0);
            }
            ExecuteTreeTable_Func(sqlQuery, parentID, ref dt, paddingChar, loopCount);
            return dt;
        }
        // ExecuteTreeTable递归方法
        private void ExecuteTreeTable_Func(string sqlQuery, string parentID, ref DataTable dt, char paddingChar, int loopCount)
        {
            bool isTree = false;
            string sqlSearch = sqlQuery;
            if (sqlQuery.Contains('{'))
            {
                isTree = true;
                sqlSearch = sqlQuery.Replace("{0}", parentID);
            }

            using (DbConnection conn = GetDbConnection())
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlSearch;
                conn.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        dt.Rows.Add(dr[0].ToString(), "".PadRight(loopCount * 2, paddingChar) + dr[1].ToString(), loopCount.ToString());
                        if (isTree) ExecuteTreeTable_Func(sqlQuery, dr[0].ToString(), ref dt, paddingChar, ++loopCount);
                    }
                }
            }
        }

        /// <summary>
        /// 获取当前节点的所有子节点(包含节点关联的元素)和递归父节点树形列表
        /// <para>例如：ExecuteTreeXml("Select ID, Name from tUSerDepartment where ParentID='{0}'", "093F207D-634B-4717-AB50-91A21F035083", "Select AgentID, Name from tUserMember where DepartmentID='{0}'", "select ID, Name, ParentID from tUserDepartment where ID='{0}'"));</para>
        /// </summary>
        /// <param name="sqlChildFmt">如：Select ID, Name from tUSerDepartment where ParentID='{0}'</param>
        /// <param name="nodeID">当前节点的值</param>
        /// <param name="sqlfmtItems">如：Select AgentID, Name, Face, State from tUserMember where DepartmentID='{0}' (DepartmentID是外键，对应部门表的主键)</param>
        /// <param name="sqlfmtParent">如：select ID, Name, ParentID from tUserDepartment where ID='{0}' (需要通过Parent来追溯父级链，并且ParentID必需是第三个查询的字段！)</param>
        /// <returns>树形XML字符串</returns>
        /// <remarks>
        /// 2013-9-5 1.0.0 by sutroon 创建
        /// 2013-9-9 2.0.0 by sutroon 重构,新增父级链表重塑和子集项目关联
        /// 2013-10-29 2.0.1 by sutroon 支持字段映射到属性功能
        /// </remarks>
        public string ExecuteTreeXml(string sqlChildFmt, string nodeID, string sqlfmtItems = "", string sqlfmtParent = "")
        {
            StringBuilder sb = new StringBuilder(), sbChild = new StringBuilder(), sbParent = new StringBuilder();
            sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?><root>");
            ExecuteTreeXmlForChild_Func(sqlChildFmt, nodeID, ref sbChild, sqlfmtItems);
            if (string.IsNullOrEmpty(sqlfmtParent))
            {
                sb.Append(sbChild.ToString());
            }
            else
            {
                sbParent.Append(sbChild.ToString());
                ExecuteTreeXmlForParent_Func(sqlfmtParent, nodeID, ref sbParent);
                sb.Append(sbParent.ToString());
            }
            sb.Append("</root>");
            return sb.ToString();
        }
        // 获取所有子集以及子集中的所有项目的方法
        private void ExecuteTreeXmlForChild_Func(string sqlfmt, string pid, ref StringBuilder sb, string sqlfmtItems = "")
        {
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            DataTable dt2;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.AppendFormat("<node {0}>", r.ToAttributeString());
                    if (string.IsNullOrEmpty(sqlfmtItems))
                    {
                        dt2 = ExecuteDataTable(string.Format(sqlfmtItems, r[0]));
                        if (dt2.Rows.Count > 0)
                        {
                            foreach (DataRow r2 in dt2.Rows)
                            {
                                sb.AppendFormat("<node {0} />", r2.ToAttributeString());
                            }
                        }
                    }
                    ExecuteTreeXmlForChild_Func(sqlfmt, r[0].ToString(), ref sb, sqlfmtItems);
                    sb.Append("</node>");
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlfmtItems))
                {
                    dt2 = ExecuteDataTable(string.Format(sqlfmtItems, pid));
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow r2 in dt2.Rows)
                        {
                            sb.AppendFormat("<node {0} />", r2.ToAttributeString());
                        }
                    }
                }
            }
        }
        // 获取当前节点所有父节点链表的方法
        private void ExecuteTreeXmlForParent_Func(string sqlfmt, string pid, ref StringBuilder sb)
        {
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.Insert(0, string.Format("<node {0}>", r.ToAttributeString()));
                    ExecuteTreeXmlForParent_Func(sqlfmt, r[2].ToString(), ref sb);
                    sb.Append("</node>");
                }
            }
        }

        /// <summary>
        /// 获取指定行的所有值的列表
        /// </summary>
        /// <typeparam name="V">指定行的数据类型</typeparam>
        /// <param name="sqlQuery">查询语句</param>
        /// <returns></returns>
        public IList<V> GetColumnValues<V>(string sqlQuery)
        {
            IDataReader idr = this.ExecuteDataTable(sqlQuery).CreateDataReader();
            IList<V> list = new List<V>();
            while (idr.Read())
            {
                list.Add((V)Convert.ChangeType(idr[0], typeof(V)));
            }
            return list;
        }

        /// <summary>
        /// 获取值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>查询的值</returns>
        /// <remarks>
        /// 2013-6-9 1.0.0 by sutroon 创建
        /// </remarks>
        public object GetValue(string sql, object defv = null)
        {
            using (DbCommand cmd = this.GetDbCommand())
            {
                cmd.CommandText = sql;
                cmd.Connection.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        return dr[0];
                    }
                }
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
            return defv;
        }

        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public int Update(string tableName, string fields, string search)
        {
            return this.ExecuteNonQuery(string.Format("update {0} set {1} where {2}", tableName, fields, search));
        }

        /// <summary>
        /// 插入数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public int Insert(string tableName, string fields, string values)
        {
            return this.ExecuteNonQuery(string.Format("insert into {0} ({1}) values ({2})", tableName, fields, values));
        }

        /// <summary>
        /// 删除数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public int Delete(string tableName, string search)
        {
            return this.ExecuteNonQuery(string.Format("delete from {0} where {1}", tableName, search));
        }

        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        /// <remarks>2013-11-3 by sutroon</remarks>
        public bool Exists(string tableName, string search, string field = "ID")
        {
            return Count(tableName, search, field) > 0;
        }

        /// <summary>
        /// 查询记录数量
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        /// <remarks>2013-11-3 by sutroon</remarks>
        public int Count(string tableName, string search, string field = "ID")
        {
            return (int)this.GetValue(string.Format("select count({0}) from {1}{2}", field, tableName, search));
        }

        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public DataTable SelectDataTable(string tableName, string search, string fields = "*")
        {
            return ExecuteDataTable(string.Format("select {0} from {1}{2}{3}", fields, tableName, search.Length > 0 ? " where " : "", search));
        }
        #endregion

        #region 获取基础对象方法

        /// <summary>
        /// 获取通用数据库连接对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbConnection GetDbConnection()
        {
            DbConnection conn = new SqlConnection();
            conn.ConnectionString = ConnectionString;
            return conn;
        }

        /// <summary>
        /// 获取通用数据库命令对象
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbCommand GetDbCommand()
        {
            return GetDbConnection().CreateCommand();
        }
        #endregion

        #region 获取SQL命令语句方法

        /// <summary>
        /// 获取表说明文档
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string SQL_GetShowTableCommonSql(string table)
        {
            return "select f.value as Common from sysobjects d left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where d.name='{0}'".Fmt(table);
        }

        /// <summary>
        /// 获取表结构
        /// <para>ColOrder=字段序号(int),Name=字段名(string),IsIdentity=标识(0|1),KeyName=主键名称(string|DbNull),DbType=类型(string),Length=占用字节数(int),Precision=长度(int),XScale=小数位数(int|DbNull),AllowDBNull=允许空(0|1),DefaultValue=默认值(string|DbNull),Common=字段说明(string|DbNull)</para>
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string Sql_GetShowStructureSql(string table)
        {
            return string.Format("SELECT ColOrder = a.colorder, Name = a.name, IsIdentity = COLUMNPROPERTY( a.id,a.name,'IsIdentity'), KeyName = (SELECT name FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))), DbType  = b.name, Length = a.length, Precision = COLUMNPROPERTY(a.id,a.name,'PRECISION'), XScale   = COLUMNPROPERTY(a.id,a.name,'Scale'), AllowDBNull= a.isnullable, DefaultValue = e.text, Common = g.[value] FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id where d.name='{0}'", table);
        }

        /// <summary>
        /// 获取显示所有表的命令
        /// </summary>
        /// <returns></returns>
        public string SQL_GetShowTablesSql()
        {
            return "select [name] from [sysobjects] where [xtype]='u'";
        }

        /// <summary>
        /// 获取显示所有存储过程的命令
        /// </summary>
        /// <returns></returns>
        public string SQL_GetShowProceduresSql()
        {
            return "select [name] from [sysobjects] where [xtype]='p'";
        }

        /// <summary>
        /// 获取显示所有视图的命令
        /// </summary>
        /// <returns></returns>
        public string SQL_GetShowViewsSql()
        {
            return "select [name] from [sysobjects] where [xtype]='v'";
        }

        /// <summary>
        /// 获取显示所有数据库的命令
        /// <para>DATABASE_NAME,DATABASE_SIZE,REMARKS</para>
        /// </summary>
        /// <returns></returns>
        public string SQL_GetShowDatabasesSql()
        {
            return "sp_databases";
        }

        /// <summary>
        /// 获取显示数据表所有字段的命令语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string SQL_GetShowFieldsSql(string tableName)
        {
            return "select name from syscolumns where id=object_id('{0}') order by colid".Fmt(tableName); ;
        }

        /// <summary>
        /// 获取对应数据库的自动增长字段语句
        /// </summary>
        /// <returns></returns>
        public string Sql_GetAutoIncrementSql()
        {
            return "identity(1,1)";
        }

        /// <summary>
        /// 获取对应数据库前N条记录的语句
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">要查找的字段，如 title,content,*等</param>
        /// <param name="condition">查询条件</param>
        /// <param name="count">记录数量</param>
        /// <returns></returns>
        public string Sql_GetSelectTopNRecordSql(string tableName, string fields = "*", string condition = "", int count = 1)
        {
            return "select top {3} {0} from {1} {2}".Fmt(fields, tableName, condition, count);
        }

        /// <summary>
        /// 获取删除表命令
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public string Sql_GetDropTableSql(string tablename)
        {
            return string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) {1}DROP TABLE [dbo].[{0}];{1}", tablename, Environment.NewLine);
        }

        /// <summary>
        /// 获取重命名表的命令
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string Sql_GetRenameTableSql(string tableName, string newName)
        {
            return string.Format("EXEC sp_rename '{0}','{1}';", tableName, newName);
        }

        /// <summary>
        /// 设置查询条件语句,建议查询条件无需带where关键词
        /// <para>包含符号=, is , like ,&gt;,&lt;,!&gt;等关键词自动补齐 where 关键词</para>
        /// <para>不包含符号且不带where 自动补齐 order by关键词</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public string Sql_SetConditionSql(string condition)
        {
            if (condition.Length > 12 && condition.TrimStart().ToLower().Substring(0, 7) == "select ") return condition;
            condition = condition.Trim();
            if (condition.Length == 0) return string.Empty;
            int iSpace = condition.IndexOf(' ');
            bool hasSymbol = false;
            foreach (string s in new string[] { "=", ">", "<", "!", " is ", " like ", " in " })
            {
                if (condition.ToLower().IndexOf(s) > -1) hasSymbol = true;
            }
            if (iSpace == -1)
            {
                return hasSymbol ? " where {0} ".Fmt(condition) : " order by {0} ".Fmt(condition);
            }
            string word = condition.Substring(0, iSpace).ToLower();
            if (word == "where" || word == "order")
            {
                return condition;
            }
            return hasSymbol ? " where {0} ".Fmt(condition) : " order by {0} ".Fmt(condition);
        }
        public string Sql_SetConditionSql(string where, string orderby)
        {
            StringBuilder sb = new StringBuilder();
            if (where.Length > 0) sb.AppendFormat("where {0}", where);
            if (orderby.Length > 0) sb.AppendFormat("order by {0}", orderby);
            return sb.ToString();
        }

        /// <summary>
        /// 设置数量限制
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public string SQL_SetLimit(string sql, int count)
        {
            sql = sql.TrimStart();
            if (sql.Substring(0, 7).ToLower() != "select ") return sql;
            return sql.Insert(6, " top {0}".Fmt(count));
        }

        public string sql_GetTruncateTableSql(string tableName)
        {
            return string.Format("Truncate Table [{0}];", tableName);
        }

        /// <summary>
        /// 获取数据库中所有数据表
        /// </summary>
        /// <param name="connectionInfo"></param>
        /// <returns></returns>
        public IList<string> SO_GetTables()
        {
            List<string> list = new List<string>();
            using (IDataReader IDr = ExecuteDataTable(SQL_GetShowTablesSql()).CreateDataReader())
            {
                while (IDr.Read())
                {
                    list.Add(IDr[0].ToString());
                }
            }
            list.Sort();
            return list;
        }

        /// <summary>
        /// 获取数据库中所有存储过程
        /// </summary>
        /// <returns></returns>
        public IList<string> SO_GetProcedures()
        {
            List<string> list = new List<string>();
            using (IDataReader IDr = ExecuteDataTable(SQL_GetShowProceduresSql()).CreateDataReader())
            {
                while (IDr.Read())
                {
                    list.Add(IDr[0].ToString());
                }
            }
            list.Sort();
            return list;
        }

        /// <summary>
        /// 获取数据库中所有视图
        /// </summary>
        /// <returns></returns>
        public IList<string> SO_GetViews()
        {
            List<string> list = new List<string>();
            using (IDataReader IDr = ExecuteDataTable(SQL_GetShowViewsSql()).CreateDataReader())
            {
                while (IDr.Read())
                {
                    list.Add(IDr[0].ToString());
                }
            }
            list.Sort();
            return list;
        }

        /// <summary>
        /// 获取指定数据表的所有字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public IList<string> SO_GetFields(string tableName)
        {
            IList<string> list = new List<string>();
            foreach (DataRow r in ExecuteDataTable(SQL_GetShowFieldsSql(tableName)).Rows)
            {
                list.Add(r[0].ToString());
            }
            return list;
        }

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="connInfo"></param>
        /// <param name="tablenames"></param>
        public void SO_DropTable(params string[] tablenames)
        {
            foreach (string s in tablenames)
            {
                ExecuteNonQuery(Sql_GetDropTableSql(s));
            }
        }

        /// <summary>
        /// 确保数据库存在,如果不存在则创建
        /// </summary>
        /// <param name="dbName"></param>
        /// <remarks>2014-4-9</remarks>
        public bool So_EnsureDatabase(string dbName, out string msg)
        {
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "use " + dbName;
                    conn.Open();
                    try
                    {
                        cmd.ExecuteScalar();
                        msg = string.Empty;
                        return true;
                    }
                    catch
                    {
                        try
                        {
                            cmd.CommandText = string.Format("create database  {0}", dbName);
                            cmd.ExecuteNonQuery();
                            msg = string.Format("重建数据库 {0} 成功!", dbName);
                            return true;
                        }
                        catch (Exception ex)
                        {
                            msg = string.Format("重建数据库 {0} 失败: {1}.", dbName, ex.Message);
                            return false;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 确保数据表存在,如果不存在则创建
        /// <para>获取是否存在或创建提示请调用MessageSet</para>
        /// <para>如：SO_EnsureTable("tMyTable","ID int identity(1,1) primary key, Name varchar(32), State smallint not null default 0")</para>
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="fieldsdefinesql"></param>
        /// <remarks>2014-2-21</remarks>
        public bool SO_EnsureTable(string tablename, string fieldsdefinesql, out string msg)
        {
            TableName = tablename;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select top 1 * from " + tablename;
                    conn.Open();
                    try
                    {
                        cmd.ExecuteScalar();
                        msg = string.Empty;
                        return true;
                    }
                    catch
                    {
                        try
                        {
                            cmd.CommandText = string.Format("create table {0} ({1})", tablename, fieldsdefinesql);
                            cmd.ExecuteNonQuery();
                            msg = string.Format("重建数据表 {0} 成功!", tablename);
                            return true;
                        }
                        catch (Exception ex)
                        {
                            msg = string.Format("重建数据表 {0} 失败: {1}.", tablename, ex.Message);
                            return false;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 确保数据字段存在,如果不存在则创建
        /// <para>获取是否存在或创建提示请调用MessageSet</para>
        /// <para>如：</para>
        /// <para>DBH.TableName="tMyTable" (如果已经调用过SO_EnsureTable,则默认数据表是SO_EnsureTable传递的数据表参数)</para>
        /// <para>SO_EnsureField("MyFieldName","datetime not null default getdate()");</para>
        /// <para>此方法会默认判断数据表是否存在,因此无需担心数据表不存在而出错</para>
        /// </summary>
        /// <param name="fieldname"></param>
        /// <param name="fielddefinesql"></param>
        /// <param name="tablename"></param>
        /// <remarks>2014-2-21</remarks>
        public bool SO_EnsureField(string fieldname, string fielddefinesql, out string msg, string tablename = null)
        {
            if (string.IsNullOrEmpty(tablename)) tablename = TableName;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select top 1 * from " + tablename;
                    conn.Open();
                    try
                    {
                        cmd.ExecuteScalar();
                    }
                    catch
                    {
                        msg = string.Format("添加字段 {0} 失败,原因是：数据表 {1} 不存在!", fieldname, tablename);
                        return false;
                    }
                    try
                    {
                        cmd.CommandText = string.Format("select {0} from {1}", fieldname, tablename);
                        cmd.ExecuteScalar();
                        msg = string.Empty;
                        return true;
                    }
                    catch
                    {
                        try
                        {
                            cmd.CommandText = string.Format("alter table {0} add {1} {2}", tablename, fieldname, fielddefinesql);
                            cmd.ExecuteNonQuery();
                            msg = string.Format("重建字段 {0}.{1} {2} 成功!", tablename, fieldname, fielddefinesql);
                            return true;
                        }
                        catch (Exception ex)
                        {
                            msg = string.Format("重建字段 {0}.{1} 失败: {2}.", tablename, fieldname, ex.Message);
                            return false;
                        }
                    }

                }
            }
        }

        /// <summary>
        /// 复制数据库结构和数据
        /// </summary>
        /// <param name="connInfoFrom"></param>
        /// <param name="connInfoTo"></param>
        public void SO_CopyDatabase()
        {
            string sqlStructure = SO_GetCreateTableStructures().Values.ToString("");
            string sqldata = SO_ExportTableDataAsSqls(SO_GetTables().ToArray()).Values.ToString("");
            SO_DropTable(SO_GetTables().ToArray());
            ExecuteNonQuery(sqlStructure);
            ExecuteNonQuery(sqldata);
        }

        /// <summary>
        /// 获取所有数据库列表
        /// </summary>
        /// <returns></returns>
        public IList<string> SO_GetDatabases()
        {
            IList<string> list = new List<string>();
            DataTable dt = this.ExecuteDataTable(this.SQL_GetShowDatabasesSql());
            foreach (DataRow r in dt.Rows)
            {
                list.Add(r["DATABASE_NAME"].ToString());
            }
            return list;
        }
        #endregion

        #region 其他转换操作

        /// <summary>
        /// 托管环境数据库类型名称转换为产品环境数据库类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="clrTypeName"></param>
        /// <returns></returns>
        public string FieldTypeNameToDbTypeName(string clrTypeName)
        {
            clrTypeName = clrTypeName.ToLower();
            switch (clrTypeName)
            {
                case "int32": return "int";
                case "string": return "varchar(255)";
                case "int16": return "smallint";
                case "boolean": return "bit";
                case "guid": return "uniqueidentifier";
                case "datetime": return "datetime";
                case "decimal": return "money";
                case "int64": return "bigint";
                case "byte[]": return "image";
                case "single": return "real";
                case "double": return "float";
                case "datetimeoffset": return "datetimeoffset(7)";
                case "timespan": return "time(7)";
                case "system.xml.linq.xelement": return "xml";
            }
            return clrTypeName;
        }

        /// <summary>
        /// 产品环境数据库类型名称转换为托管环境数据库类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="clrTypeName"></param>
        /// <returns></returns>
        public string DbTypeNameToFieldTypeName(string clrTypeName, bool isNullable = true)
        {
            clrTypeName = clrTypeName.ToLower();
            switch (clrTypeName)
            {
                case "nvarchar":
                case "varchar":
                case "ntext":
                case "text":
                case "nchar":
                case "char":
                case "xml": return "string";
                case "int": return isNullable ? "int?" : "int";
                case "smallint": return isNullable ? "short?" : "short";
                case "uniqueidentifier": return isNullable ? "Guid?" : "Guid";
                case "tinyint": return isNullable ? "byte?" : "byte";
                case "datetime":
                case "smalldatetime":
                case "timestamp": return isNullable ? "DateTime?" : "DateTime";
                case "bit": return isNullable ? "bool?" : "bool";
                case "money":
                case "smallmoney": return isNullable ? "decimal?" : "decimal";
                case "float": return isNullable ? "double?" : "double";
                case "real": return isNullable ? "single?" : "single";
                case "varbinary":
                case "binary":
                case "image": return "byte[]";
                case "bigint": return isNullable ? "long?" : "long";
                case "datetimeoffset(7)": return "datetimeoffset";
                case "time(7)": return "timespan";
                case "variant": return "object";
                default:
                    return clrTypeName;

            }
        }

        /// <summary>
        /// 托管环境数据库数据类型转换成C#数据类型
        /// </summary>
        /// <param name="fieldTypeName">数据库字段类型</param>
        /// <param name="isNullable">是否允许null</param>
        /// <returns>C#数据类型</returns>
        public string FieldTypeToClrTypeName(string fieldTypeName, bool isNullable = true)
        {
            string sOut = fieldTypeName;
            switch (fieldTypeName.ToLower())
            {
                case "string": return "string";
                case "int32": return isNullable ? "int?" : "int";
                case "int16": return isNullable ? "short?" : "short";
                case "byte": return isNullable ? "byte?" : "byte";
                case "datetime": return isNullable ? "DateTime?" : "DateTime";
                case "boolean": return isNullable ? "bool?" : "bool";
                case "decimal": return isNullable ? "decimal?" : "decimal";
                case "double": return isNullable ? "double?" : "double";
                case "guid": return isNullable ? "Guid?" : "Guid";
                case "byte[]": return "byte[]";
                case "int64": return isNullable ? "long?" : "long";
                default:
                    return string.Format("{0}", sOut);
            }
        }

        /// <summary>
        /// C#数据类型转换为托管环境数据库数据类型
        /// </summary>
        /// <param name="clrType"></param>
        /// <returns></returns>
        public DbType ClrTypeToDbType(Type clrType)
        {
            if (clrType.IsGType<int>()) return DbType.Int32;
            if (clrType == typeof(string) || clrType.IsGType<DateTime>()) return DbType.String;
            if (clrType.IsGType<double>()) return DbType.Double;
            if (clrType.IsGType<bool>()) return DbType.Boolean;
            if (clrType.IsGType<short>()) return DbType.Int16;
            if (clrType.IsGType<Guid>()) return DbType.Guid;
            if (clrType.IsGType<decimal>()) return DbType.Decimal;
            if (clrType == typeof(System.Xml.Linq.XElement)) return DbType.Xml;
            if (clrType == typeof(byte[])) return DbType.Binary;
            if (clrType == typeof(object)) return DbType.Object;
            if (clrType.IsGType<long>()) return DbType.Int64;
            return DbType.String;
        }

        /// <summary>
        /// C#数据类型转为SQL数据类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public string ClrTypeToDbTypeName(Type type)
        {
            if (type.IsGType<int>()) return "int";
            if (type == typeof(string)) return "nvarchar";
            if (type.IsGType<short>()) return "smallint";
            if (type.IsGType<DateTime>()) return "datetime";
            if (type.IsGType<bool>()) return "bit";
            if (type.IsGType<Guid>()) return "uniqueidentifier";
            if (type == typeof(byte[])) return "image";
            if (type.IsGType<double>()) return "float";
            if (type.IsGType<decimal>()) return "money";
            if (type == typeof(System.Xml.Linq.XElement)) return "xml";
            if (type.IsGType<byte>()) return "tinyint";
            if (type.IsGType<long>()) return "bigint";
            if (type.IsGType<TimeSpan>()) return "time(7)";
            if (type.IsGType<DateTimeOffset>()) return "datetimeoffset(7)";
            if (type.IsGType<Single>()) return "real";
            return type.ToString().ToLower();
        }

        /// <summary>
        /// 格式化为指定数据库产品的sql命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string SQL_FormatSql(string sql)
        {
            return sql;
        }

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <param name="dbProduct"></param>
        /// <param name="serverName"></param>
        /// <param name="loginID"></param>
        /// <param name="password"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        public string GetConnectionString(string serverName, string loginID, string password, string database = "")
        {
            this.ServerName = serverName;
            this.DatabaseName = database;
            return "server={0};uid={1};pwd={2};{3};pooling=true;min pool size=10;max pool size=300;connection lifetime=0;".Fmt(serverName, loginID, password, database.Length > 0 ? "database={0};".Fmt(database) : "");
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool TestConnection(out string message)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                try
                {
                    conn.Open();
                    message = "连接成功";
                    return true;
                }
                catch (DbException e)
                {
                    message = e.Message;
                    return false;
                }
            }
        }
        #endregion

        #region 存储过程操纵方法

        /// <summary>
        /// 执行存储过程,返回新的参数值集合
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public Dictionary<string, object> ExecuteProcedureNonQuery(string procedureName, params DbParameter[] dbParameters)
        {
            int iOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.Parameters.AddRange(dbParameters);
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.CommandText = procedureName;
                conn.Open();
                iOut = oCmd.ExecuteNonQuery();
            }
            return dbParameters.ToDictionary();
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="outParameters"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, ref Dictionary<string, object> outParameters, params DbParameter[] dbParameters)
        {
            DataTable oDt = new DataTable();
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = procedureName;
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.Parameters.AddRange(dbParameters);
                DbDataAdapter oDa = new SqlDataAdapter();
                oDa.SelectCommand = oCmd;
                conn.Open();
                oDa.Fill(oDt);
            }
            if (outParameters == null) return oDt;
            foreach (DbParameter para in dbParameters)
            {
                outParameters[para.ParameterName] = para.Value;
            }
            return oDt;
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, params DbParameter[] dbParameters)
        {
            Dictionary<string, object> dict = null;
            return this.ExecuteProcedureDataTable(procedureName, ref dict, dbParameters);
        }

        #endregion

        #region 备份还原

        /// <summary>
        /// 获取创建表的语句列表
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        /// <remarks>
        /// 2013-8-2 2.0.0 by sutroon 重构 太完美了^_^
        /// </remarks>
        public Dictionary<string, string> SO_GetCreateTableStructures(params string[] tableNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            IList<string> lstTables = tableNames.Length == 0 ? SO_GetTables() : tableNames;
            using (DbConnection conn = GetDbConnection())
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("-- Created By DBKit {0}{1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), Environment.NewLine);
                DbCommand cmd = conn.CreateCommand();
                string a = string.Format(",{0});", Environment.NewLine), b = string.Format("{0});", Environment.NewLine);
                conn.Open();
                foreach (string s in lstTables)
                {
                    sb.AppendFormat("{1}create table [{0}]{1}", s, Environment.NewLine);
                    sb.AppendLine("(");
                    cmd.CommandText = Sql_GetShowStructureSql(s);
                    using (IDataReader idr = cmd.ExecuteReader())
                    {
                        while (idr.Read())
                        {
                            if (idr["KeyName"] != DBNull.Value)
                            {
                                sb.AppendFormat("\t[{0}] {1} primary key {2} {3},{4}", idr["Name"], idr["DbType"], (idr["IsIdentity"].ToString() == "1" ? "identity(1,1)" : ""), (idr["DefaultValue"] == DBNull.Value ? "" : "default " + idr["DefaultValue"].ToString().Replace("(newid())", "newid()")), Environment.NewLine);
                            }
                            else
                            {
                                sb.AppendFormat("\t[{0}] {1}{2} {3} {4},{5}", idr["Name"], idr["DbType"], (idr["DbType"].ToString().IsIn("varchar", "nvarchar", "char", "nchar") ? "(" + idr["Length"] + ")" : ""), (idr["AllowDBNull"].ToString() == "1" ? "not null" : ""), (idr["DefaultValue"] == DBNull.Value ? "" : "default " + idr["DefaultValue"].ToString().Replace("(", "").Replace(")", "").Replace("getdate", "getdate()").Replace("newid", "newid()")), Environment.NewLine);
                            }
                        }
                    }
                    sb.AppendLine(");");
                    sb.AppendLine();
                    dict.Add(s, sb.ToString().Replace(a, b));
                    sb.Clear();
                }
                cmd.Dispose();
            }
            return dict;
        }

        /// <summary>
        /// 获取创建存储过程的语句列表
        /// </summary>
        /// <param name="procNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_GetCreateProcedureStructures(params string[] procNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            StringBuilder sb;
            IDataReader idr;
            string sFmt = SQL_FormatSql("[{0}]");
            IList<string> lstTables = procNames.Length == 0 ? SO_GetProcedures() : procNames;
            foreach (string s in lstTables)
            {
                sb = new StringBuilder();
                idr = ExecuteDataTable(string.Format("exec sp_helptext '{0}'", s)).CreateDataReader();
                while (idr.Read())
                {
                    sb.AppendLine(idr[0].ToString());
                }
                dict.Add(s, sb.ToString());
            }
            return dict;
        }

        /// <summary>
        /// 获取创建视图的语句列表
        /// </summary>
        /// <param name="viewNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_GetCreateViewStructures(params string[] viewNames)
        {
            return SO_GetCreateProcedureStructures(viewNames);
        }

        /// <summary>
        /// 导出Sql数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public string SO_ExportTableDataAsSql(string tableName, string condition = "")
        {
            StringBuilder sb = new StringBuilder();
            using (DbDataReader ddr = this.ExecuteDataTable("select * from {0} {1}".Fmt(tableName, Sql_SetConditionSql(condition))).CreateDataReader())
            {
                if (!ddr.HasRows) return "";

                StringBuilder sbFields;
                StringBuilder sbValues;

                sb.AppendLine();
                sb.AppendLine("-- set identity_insert [" + tableName + "] on");
                sb.AppendLine();
                while (ddr.Read())
                {
                    sbValues = new StringBuilder();
                    sbFields = new StringBuilder();
                    for (int i = 0; i < ddr.FieldCount; i++)
                    {
                        if (ddr[i] == DBNull.Value) continue;
                        sbFields.AppendFormat("[{0}],", ddr.GetName(i));
                        string s = FieldTypeToClrTypeName(ddr.GetDataTypeName(i));
                        switch (FieldTypeToClrTypeName(ddr.GetDataTypeName(i), false))
                        {
                            case "string":
                            case "DateTime":
                            case "xml":
                            case "image":
                            case "Guid":
                                sbValues.AppendFormat("'{0}',", ddr[i]);
                                break;
                            case "bool":
                                sbValues.AppendFormat("{0},", (ddr[i].ToString() == "True" ? 1 : 0));
                                break;
                            default:
                                sbValues.AppendFormat("{0},", ddr[i]);
                                break;
                        }
                    }
                    sb.AppendFormat("insert into [{0}] ({1}) values ({2});", tableName, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
                    sb.AppendLine();
                    sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        /// <summary>
        /// 导出所有Sql数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_ExportTableDataAsSqls(params string[] tableNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            DbDataReader ddr;
            StringBuilder sb, sbFields, sbValues;
            foreach (string s in tableNames)
            {
                using (ddr = this.ExecuteDataTable("select * from {0}".Fmt(s)).CreateDataReader())
                {
                    if (!ddr.HasRows) continue;

                    sb = new StringBuilder();

                    sb.AppendLine();
                    sb.AppendLine("-- set identity_insert [" + s + "] on");
                    sb.AppendLine();
                    while (ddr.Read())
                    {
                        sbValues = new StringBuilder();
                        sbFields = new StringBuilder();
                        for (int i = 0; i < ddr.FieldCount; i++)
                        {
                            if (ddr[i] == DBNull.Value) continue;
                            sbFields.AppendFormat("[{0}],", ddr.GetName(i));
                            switch (FieldTypeToClrTypeName(ddr.GetDataTypeName(i), false))
                            {
                                case "string":
                                case "DateTime":
                                case "xml":
                                case "image":
                                case "Guid":
                                    sbValues.AppendFormat("'{0}',", ddr[i]);
                                    break;
                                case "bool":
                                    sbValues.AppendFormat("{0},", (ddr[i].ToString() == "True" ? 1 : 0));
                                    break;
                                default:
                                    sbValues.AppendFormat("{0},", ddr[i]);
                                    break;
                            }
                        }
                        sb.AppendFormat("insert into [{0}] ({1}) values ({2});", s, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
                        sb.AppendLine();
                        sb.AppendLine();
                    }
                    dict.Add(s, sb.ToString());
                }
            }
            return dict;
        }

        /// <summary>
        /// 备份整个数据库
        /// </summary>
        /// <returns></returns>
        public void SO_BackupDatabase()
        {

        }

        /// <summary>
        /// 还原整个数据库
        /// </summary>
        /// <param name="structuresql"></param>
        /// <param name="datasql"></param>
        public void SO_RebackDatabase(string structuresql, string datasql)
        {
            if (structuresql.Length > 0)
            {
                SO_DropTable(SO_GetTables().ToArray());
                this.ExecuteNonQuery(structuresql);
            }
            if (datasql.Length > 0) this.ExecuteNonQuery(datasql);
        }

        /// <summary>
        /// 获取从模型更新数据库的语句
        /// <para>2013-8-2 by sutroon</para>
        /// </summary>
        /// <param name="doc"></param>
        /// <returns></returns>
        public string SO_GetUpdateTableStructureSqlsByXml(XElement doc, params string[] tableNames)
        {
            XElement docDb = XElement.Parse(string.Format("<?xml version=\"1.0\" encoding=\"utf-8\"?><db>{0}</db>", SO_ExportTableStructureAsXmls(tableNames).Values.ToString(Environment.NewLine)));
            // 模型中的所有表名
            IEnumerable<string> lstTablesXml = from t in doc.Elements() select t.Attribute("Name").Value;
            // 数据库中的所有表名
            IEnumerable<string> lstTablesDb = from t in docDb.Elements() select t.Attribute("Name").Value;
            // 模型和数据库中表的交集
            IEnumerable<string> lstTableIntersect = lstTablesXml.Intersect(lstTablesDb);
            StringBuilder sb = new StringBuilder();
            XElement xTableXml, xTableDb, xField, xFieldtmp;
            IEnumerable<string> lstFieldsXml, lstFieldsDb, lstFieldsAdd, lstFieldsDrop, lstFieldsIntersect;
            foreach (string tbl in lstTableIntersect)
            {
                xTableXml = doc.Elements().Single(a => a.Attribute("Name").Value == tbl);
                xTableDb = docDb.Elements().Single(a => a.Attribute("Name").Value == tbl);
                lstFieldsXml = from f in xTableXml.Elements() select f.Attribute("Name").Value;
                lstFieldsDb = from f in xTableDb.Elements() select f.Attribute("Name").Value;
                // 数据库中有的而模型没有的则移除
                lstFieldsDrop = lstFieldsDb.Except(lstFieldsXml);
                foreach (string f in lstFieldsDrop)
                {
                    sb.AppendFormat("alter table [{0}] drop [{1}];{2}", tbl, f, Environment.NewLine);
                }
                // 数据库中没有而模型有的则增加
                lstFieldsAdd = lstFieldsXml.Except(lstFieldsDb);
                foreach (string f in lstFieldsAdd)
                {
                    xField = xTableXml.Elements().Single(a => a.Attribute("Name").Value == f);
                    sb.AppendFormat("alter table [{0}] add [{1}] {2}{3} {4} {5};{6}", tbl, f, xField.Attribute("Type").Value, (xField.Attribute("Type").Value.IsIn("varchar", "nvarchar", "char", "nchar") ? "(" + xField.Attribute("Length").Value + ")" : ""), (xField.Attribute("Null").Value == "0" ? "not null" : ""), (xField.Attribute("Default").Value.Length == 0 ? "" : "default " + xField.Attribute("Default").Value), Environment.NewLine);
                }
                // 模型和数据库中字段的交集
                lstFieldsIntersect = lstFieldsDb.Intersect(lstFieldsXml);
                foreach (string f in lstFieldsIntersect)
                {
                    xField = xTableXml.Elements().Single(a => a.Attribute("Name").Value == f);
                    xFieldtmp = xTableDb.Elements().Single(a => a.Attribute("Name").Value == f);
                    if (xField.Attribute("Type").Value != xFieldtmp.Attribute("Type").Value)
                    {
                        sb.AppendFormat("alter table [{0}] alter column [{1}] {2}{3} {4} {5};{6}", tbl, f, xField.Attribute("Type").Value, (xField.Attribute("Type").Value.IsIn("varchar", "nvarchar", "char", "nchar") ? "(" + xField.Attribute("Length").Value + ")" : ""), (xField.Attribute("Null").Value == "0" ? "not null" : ""), (xField.Attribute("Default").Value.Length == 0 ? "" : "default " + xField.Attribute("Default").Value), Environment.NewLine);
                    }
                }

            }
            // 数据库中有而模型没有的表则丢弃
            IEnumerable<string> lstTableDrop = lstTablesDb.Except(lstTablesXml);
            if (lstTableDrop.Count() > 0) sb.AppendLine(lstTableDrop.ToString(Environment.NewLine, "drop table [{0}];"));
            // 数据库中没有而模型中有的表则创建
            IEnumerable<string> lstTableCreate = lstTablesXml.Except(lstTablesDb);
            if (lstTableCreate.Count() > 0)
            {
                sb.AppendLine(SO_GetCreateTableStructureSqlsByXml(doc, lstTableCreate.ToArray()));
            }
            return sb.ToString();
        }
        public string SO_GetCreateTableStructureSqlsByXml(XElement doc, params string[] tableNames)
        {
            // 模型中的所有表名
            IEnumerable<string> lstTableCreate = null;
            if (tableNames.Length == 0)
            {
                lstTableCreate = from t in doc.Elements() select t.Attribute("Name").Value;
            }
            else
            {
                lstTableCreate = from t in doc.Elements() where tableNames.Contains(t.Attribute("Name").Value) select t.Attribute("Name").Value;
            }
            XElement xTableXml;
            StringBuilder sb = new StringBuilder();
            foreach (string tbl in lstTableCreate)
            {
                xTableXml = doc.Elements().Single(a => a.Attribute("Name").Value == tbl);
                sb.AppendFormat("create table [{0}]{1}", tbl, Environment.NewLine);
                sb.AppendLine("(");
                foreach (XElement f in xTableXml.Elements())
                {
                    if (f.Attribute("Key").Value == "1")
                    {
                        sb.AppendFormat("\t[{0}] {1} primary key{2}{3};{4}", f.Attribute("Name").Value, f.Attribute("Type").Value, (f.Attribute("Type").Value == "int" ? " identity(1,1)" : ""), (f.Attribute("Default").Value.Length == 0 ? "" : " default " + f.Attribute("Default").Value), Environment.NewLine);
                    }
                    else
                    {
                        sb.AppendFormat("\t[{0}] {1}{2} {3} {4};{5}", f.Attribute("Name").Value, f.Attribute("Type").Value, (f.Attribute("Type").Value.IsIn("varchar", "nvarchar", "char", "nchar") ? "(" + f.Attribute("Length").Value + ")" : ""), (f.Attribute("Null").Value == "0" ? "not null" : ""), (f.Attribute("Default").Value.Length == 0 ? "" : "default " + f.Attribute("Default").Value), Environment.NewLine);
                    }
                }
                sb.AppendLine(");");
                sb.AppendLine();
            }
            return sb.ToString();
        }

        /// <summary>
        /// 获取特性数据库模型XML文档
        /// <para>Null=0|1,Key=0|1</para>
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_ExportTableStructureAsXmls(params string[] tableNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            IList<string> lstTables = tableNames.Length == 0 ? SO_GetTables() : tableNames;
            using (DbConnection conn = GetDbConnection())
            {
                StringBuilder sb = new StringBuilder();
                DbCommand cmd = conn.CreateCommand();
                conn.Open();
                foreach (string tbl in lstTables)
                {
                    sb.AppendFormat("<table Name=\"{0}\" Common=\"\">", tbl);
                    cmd.CommandText = Sql_GetShowStructureSql(tbl);
                    using (IDataReader idr = cmd.ExecuteReader())
                    {
                        while (idr.Read())
                        {
                            sb.AppendFormat("<field Name=\"{0}\" Type=\"{1}\" Length=\"{2}\" Null=\"{3}\" Key=\"{4}\" Default=\"{5}\" Extra=\"{6}\" Common=\"{7}\" />", idr["Name"], idr["DbType"], idr["Precision"], idr["AllowDBNull"], idr["KeyName"] == DBNull.Value ? "0" : "1", idr["DefaultValue"].ToString().Replace("(", "").Replace(")", "").Replace("newid", "newid()").Replace("getdate", "getdate()"), "", idr["Common"]);
                        }
                    }
                    sb.AppendLine("</table>");
                    dict.Add(tbl, sb.ToString());
                    sb.Clear();
                }
                cmd.Dispose();
            }
            return dict;
        }

        /// <summary>
        /// 获取标准数据库模型XML文档
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_ExportTableStructureAsStandardXmls(params string[] tableNames)
        {
            string slen, stype;
            DataTable dtFields;
            StringBuilder sb = new StringBuilder();
            Dictionary<string, string> dict = new Dictionary<string, string>();
            foreach (string table in tableNames)
            {
                sb.AppendFormat("<table Name=\"{0}\">", table);
                dtFields = this.ExecuteDataTable(Sql_GetShowStructureSql(table));
                foreach (DataRow r in dtFields.Rows)
                {
                    sb.AppendFormat("<field Name=\"{0}\" Type=\"{1}\" Length=\"{2}\" Null=\"{3}\" Key=\"{4}\" Default=\"{5}\" Extra=\"{6}\" Common=\"{7}\" />", r["Name"], r["DbType"], r["Precision"], r["AllowDBNull"], r["KeyName"] == DBNull.Value ? "0" : "1", r["DefaultValue"], "", r["Common"]);
                }
                sb.AppendFormat("</table>");
                dict.Add(table, sb.ToString());
                sb.Clear();
            }
            return dict;
        }

        /// <summary>
        /// Sql 语法检查 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string SO_ParseSql(string sql)
        {
            if (string.IsNullOrEmpty(sql)) return "命令无效";
            sql = string.Format("SET PARSEONLY ON;{0}", sql);

            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = sql;
                conn.Open();
                try
                {
                    this.ExecuteNonQuery(sql);
                }
                catch (DbException de)
                {
                    return de.Message;
                }
            }
            return string.Empty;
        }
        #endregion

        #region 自定义事务类
        class myTran
        {
            public string queryString { get; set; }
            public List<DbParameter> parameters { get; set; }

            public myTran(string queryString, List<DbParameter> parameters)
            {
                this.queryString = queryString;
                this.parameters = parameters;
            }
        }
        #endregion

        #region 添加事务
        private List<myTran> TranList = new List<myTran>(); //事务集合
        public void AddTran(string queryString, List<DbParameter> parameters)
        {
            var tran = new myTran(queryString, parameters);
            TranList.Add(tran);
        }

        public void AddTran(string queryString, DbParameter parameter)
        {
            List<DbParameter> paras = new List<DbParameter>();
            if (parameter != null)
                paras.Add(parameter);
            var tran = new myTran(queryString, paras);
            TranList.Add(tran);
        }
        #endregion

        #region 清除事务
        void ClearTran()
        {
            TranList.Clear();
        }
        #endregion

        #region 执行事务
        public void ExecuteTran()
        {
            try
            {
                DbConnection Conn = GetDbConnection();
                using (DbTransaction tran = Conn.BeginTransaction())
                {
                    try
                    {
                        if (Conn.State == ConnectionState.Closed) Conn.Open();
                        TranList.ForEach(m =>
                        {
                            using (var cmd = Conn.CreateCommand())
                            {
                                cmd.CommandText = m.queryString;
                                cmd.Transaction = tran;
                                m.parameters.ForEach(n =>
                                {
                                    cmd.Parameters.Add(n);
                                });
                                cmd.ExecuteNonQuery();
                            }
                        });
                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                    finally
                    {
                        ClearTran();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ClearTran();
            }
        }
        #endregion

        public string SQL_GetCreateTableSql()
        {
            return string.Format("create table MyTableName ({0}ID int identity(1,1) primary key -- 主键编号,{0}ExTags varchar(16) -- 扩展标签,{0}State smallint not null default 1 -- 状态,{0}CreatedTime datetime not null default getdate() -- 创建时间{0});", Environment.NewLine);
        }

        public string SQL_GetCreateProcedureSql()
        {
            return string.Format("create procedure MyProcedureName{0}@ID varchar(40), --订单ID{0}@ResultCode INT OUTPUT --返回错误码{0}AS{0}BEGIN{0}...{0}END{0}GO{0}", Environment.NewLine);
        }

        public string SQL_GetRenameProcedureStructureSql()
        {
            return "exec sp_rename 'oldProcedureName','newProcedureName','OBJECT'";
        }

        public string SQL_GetDropProcedureSql()
        {
            return "drop procedure MyProcedureName";
        }

        public string SQL_GetExecProcedureSql(string itemName)
        {
            return string.Format("exec {0} @p1=1,@p2=2,@p3 output", itemName);
        }

        public string SQL_GetCreateViewSql()
        {
            return string.Format("create view MyViewName --创建视图{0}AS{0}...{0}", Environment.NewLine);
        }

        public string SQL_GetRenameViewSql()
        {
            return "exec sp_rename 'MyViewNameOld' ,'MyViewNameNew'";
        }

        public string SQL_GetDropViewSql()
        {
            return "drop view MyViewName --删除视图";
        }

        public string SQL_GetExecViewSql(string itemName)
        {
            return "select * from " + itemName;
        }
    }
}
